﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;
using System.Diagnostics;
using System.Collections;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Excel;
using Microsoft.Office.Interop;
using System.Runtime.InteropServices;
using System.Threading;

namespace template_dll
{
    public partial class ExpToExcel
    {
        public bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        if (col.Index < table.ColumnCount - 1) sw.Write(col.HeaderText + "\t");
                        else sw.Write(col.HeaderText);

                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        if (y < col_n.Count - 1)
                            sw.Write(table[col_n[y], i].Value.ToString() + "\t");
                        else sw.Write(table[col_n[y], i].Value.ToString());
                    sw.Write("\r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        public bool SaveDGVToCSVfile(string filename, System.Data.DataTable table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataColumn col in table.Columns)
                {
                    if (col.Ordinal < table.Columns.Count - 1) sw.Write(col.ColumnName + "\t");
                    else sw.Write(col.ColumnName);
                    col_n.Add(col.Ordinal);
                }
                sw.WriteLine();
                int x = table.Rows.Count;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        if (y < col_n.Count - 1)
                            sw.Write(table.Rows[i][col_n[y]] + "\t");
                        else sw.Write(table.Rows[i][col_n[y]]);
                    sw.Write("\r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        public bool SaveDGVToCSVfile(string filename, System.Data.DataTable[] table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);
                foreach (System.Data.DataTable tbl in table)
                {
                    List<int> col_n = new List<int>();
                    foreach (DataColumn col in tbl.Columns)
                    {
                        if (col.Ordinal < tbl.Columns.Count - 1) sw.Write(col.ColumnName + "\t");
                        else sw.Write(col.ColumnName);
                        col_n.Add(col.Ordinal);
                    }
                    sw.WriteLine();
                    int x = tbl.Rows.Count;

                    for (int i = 0; i < x; i++)
                    {
                        for (int y = 0; y < col_n.Count; y++)
                            if (y < col_n.Count - 1)
                                sw.Write(tbl.Rows[i][col_n[y]] + "\t");
                            else sw.Write(tbl.Rows[i][col_n[y]]);
                        sw.Write("\r\n");
                    }
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        public Hashtable CheckExcellProcesses(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            int iCount = 0;
            foreach (Process ExcelProcess in AllProcesses)
            {
                myHashtable.Add(ExcelProcess.Id, iCount);
                iCount = iCount + 1;
            }
            return myHashtable;
        }

        public void KillExcel(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            foreach (Process ExcelProcess in AllProcesses)
            {
                if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                    ExcelProcess.Kill();
            }
            AllProcesses = null;
        }

        public void ImportExcel(System.Data.DataTable table)
        {
            try
            {
                OpenFileDialog dialog = new OpenFileDialog();
                dialog.Filter = "MS Excel | *.xls";
                dialog.Filter = "MS Excel | *.xlsx";
                if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    OleDbConnection connection = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", dialog.FileName));
                    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [чеки$] ", connection);
                    adapter.Fill(table);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        public void ExportToExcel(DataGridView grid)
        {
            if (grid.RowCount == 0)
            {
                MessageBox.Show("Пустой список товаров");
                return;
            }
            Microsoft.Office.Interop.Excel.Application Exl = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb;

            XlReferenceStyle RefStyle = Exl.ReferenceStyle;

            String TemplatePath = System.Windows.Forms.Application.StartupPath + @"\Экспорт данных.xltx";
            wb = Exl.Workbooks.Add();
            //try
            //{
            //    wb = Exl.Workbooks.Add(TemplatePath); // !!! 
            //}
            //catch (System.Exception ex)
            //{
            //    throw new Exception("Не удалось загрузить шаблон для экспорта " + TemplatePath + "\n" + ex.Message);
            //}
            Microsoft.Office.Interop.Excel.Worksheet ws = wb.Worksheets.get_Item(1) as Microsoft.Office.Interop.Excel.Worksheet;

            for (int j = 0; j < grid.Columns.Count; ++j)
            {
                object start = 1;
                object end = 50;
                (ws.Cells[1, j + 1] as Range).Value2 = grid.Columns[j].HeaderText;
                (ws.Cells[1, j + 1] as Range).get_Characters(start, end).Font.Color = XlRgbColor.rgbNavy;
                for (int i = 0; i < grid.Rows.Count; ++i)
                {
                    (ws.Cells[i+1, j+1] as Range).Borders.Weight = 2;
                    (ws.Cells[i + 2, j + 1] as Range).Borders.Weight = 2;
                    if (j == 0)
                    {
                        object Val = grid.Rows[i].Cells[j].Value;
                        if (Val != null)
                        {
                            (ws.Cells[i + 2, j + 1] as Range).Value2 = Val.ToString();
                        }
                    }
                    else
                    {
                        object Val = grid.Rows[i].Cells[j].Value;
                        if (Val != null)
                            (ws.Cells[i + 2, j + 1] as Range).Value2 = Val.ToString();
                    }
                }
            }
            ws.Columns.EntireColumn.AutoFit();
            Exl.ReferenceStyle = RefStyle;
            Exl.Visible = true;
            ReleaseExcel(Exl as Object);
        }

        public void ExportToExcel(System.Data.DataTable table)
        {
            if (table.Rows.Count == 0)
            {
                MessageBox.Show("Пустой список товаров");
                return;
            }
            Microsoft.Office.Interop.Excel.Application Exl = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb;

            XlReferenceStyle RefStyle = Exl.ReferenceStyle;

            wb = Exl.Workbooks.Add();

            Microsoft.Office.Interop.Excel.Worksheet ws = wb.Worksheets.get_Item(1) as Microsoft.Office.Interop.Excel.Worksheet;
            (ws.Cells[1, 1] as Range).Value2 = "Код группы";
            (ws.Cells[1, 2] as Range).Value2 = "Название группы";
            for (int j = 0; j < table.Columns.Count; ++j)
            {
                object start = 1;
                object end = 50;
                (ws.Cells[1, j + 1] as Range).get_Characters(start, end).Font.Color = XlRgbColor.rgbMidnightBlue;
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    (ws.Cells[i + 1, j + 1] as Range).Borders.Weight = 2;
                    (ws.Cells[i + 2, j + 1] as Range).Borders.Weight = 2;
                    object Val = table.Rows[i][j].ToString();
                    //if (Val != null)
                        {
                            (ws.Cells[i + 2, j + 1] as Range).Value2 = Val.ToString();
                        }
                }
            }
            ws.Columns.EntireColumn.AutoFit();
            Exl.ReferenceStyle = RefStyle;
            Exl.Visible = true;
            ReleaseExcel(Exl as Object);
        }

        private void ReleaseExcel(object excel)
        {
            // Уничтожение объекта Excel.
            Marshal.ReleaseComObject(excel);
            // Вызываем сборщик мусора для немедленной очистки памяти
            GC.GetTotalMemory(true);
        }
    }
}

